package com.softer.chengxin.outside.dao;

import com.softer.chengxin.blacklist.domin.BlacklistVO;
import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.outside.dao.provider.OutsideDaoProvider;
import com.softer.chengxin.outside.domin.CorpVO;
import com.softer.chengxin.project.domin.ProjectInfoVO;
import com.softer.chengxin.score.entity.ScoreDetailVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author wangixnrui
 * @date 2018/10/22
 */
@Mapper
@Repository
public interface OutsideDao {

    /**
     * 获取企业年度综合评价
     * @param page 页码
     * @param qyId 企业id
     * @param year 年
     * @param dj 等级
     * @param qyName 企业名
     * @return 结果集
     */
    @SelectProvider(type = OutsideDaoProvider.class,method = "getCorpRecord")
    List<CorpVO> getCorpRecord(@Param("page")Page page,
                               @Param("qyId")Integer qyId,
                               @Param("year")String year,
                               @Param("dj")String dj,
                               @Param("qyName")String qyName);
    /**
     * 获取企业年度综合评价 条数
     * @param page 页码
     * @param qyId 企业id
     * @param year 年
     * @param dj 等级
     * @param qyName 企业名
     * @return 结果集
     */
    @SelectProvider(type = OutsideDaoProvider.class,method = "getCorpRecordCount")
    int getCorpRecordCount(@Param("qyId")Integer qyId,
                           @Param("year")String year,
                           @Param("dj")String dj,
                           @Param("qyName")String qyName);

    /**
     * 查询企业是否在黑名单里 生效
     * @param qyId 企业名
     * @return 结果集
     */
    @Select("select count(*) from company_blacklist where company_id=#{qyId} and state=1")
    int getBlack(@Param("qyId")Integer qyId);

    /**
     * 查询企业优良/负面信息列表
     * @param page 页码
     * @param infoType 信息种类
     * @param id id
     * @param dj 等级
     * @param year 年度
     * @param qyName 企业名
     * @return 结果集
     */
    @SelectProvider(type = OutsideDaoProvider.class,method = "getCorpScoreDetail")
    List<ScoreDetailVO> getCorpScoreDetail(@Param("page") Page page,
                                           @Param("infoType") Integer infoType,
                                           @Param("qyId") Integer id,
                                           @Param("dj") String dj,
                                           @Param("year") Integer year,
                                           @Param("qyName")String qyName);
    /**
     * 查询企业优良/负面信息列表 条数
     * @param page 页码
     * @param infoType 信息种类
     * @param id id
     * @param dj 等级
     * @param year 年度
     * @param qyName 企业名
     * @return 结果集
     */
    @SelectProvider(type = OutsideDaoProvider.class,method = "getCorpScoreDetailCount")
    int getCorpScoreDetailCount(@Param("infoType") Integer infoType,
                                @Param("qyId") Integer id,
                                @Param("dj") String dj,
                                @Param("year") Integer year,
                                @Param("qyName")String qyName);

    /**
     * 获取该企业下项目信息
     * @param page 页码
     * @param limit 条数
     * @param qyId 企业Id
     * @return 结果
     */
    @Select("select pro.id as id,pro.project_name as projectName,pro.property_type AS propertyTypeCode,(SELECT name FROM dic_info WHERE code = pro.property_type) AS propertyType,pro.project_area as projectArea,pro.project_develop as projectDevelop,pro.committee_time as committeeTime " +
            "from company_project pro " +
            "where pro.state=2 and pro.company_id=#{qyId} order by pro.gmt_create desc " +
            "limit #{page},#{limit}")
    List<ProjectInfoVO> getCorpProject(@Param("page")Integer page,
                                       @Param("limit")Integer limit,
                                       @Param("qyId")Integer qyId);
    /**
     * 获取该企业下项目信息 条数
     * @param qyId 企业Id
     * @return 结果
     */
    @Select("select count(*) from company_project pro " +
            "where pro.state=2 and pro.company_id=#{qyId}")
    int getCorpProjectCount(@Param("qyId")Integer qyId);

    /**
     * 获取项目负责人信息
     * @param proId 项目id
     * @return 结果
     */
    @Select("select `name` from company_person p where p.project_id=#{proId} " +
            "and p.duties=10806 and p.state=1")
    String getProManagerName(@Param("proId")Integer proId);

    /**
     * 获取黑名单列表
     * @param page 页码
     * @param limit 条数
     * @return 结果
     */
    @Select("select c.id as qyId,c.`name` as qyName,c.business_license_no as businessLicenseNo," +
            "t.score_name as scoreName,b.adder as adder,date(DATE_FORMAT(b.gmt_create, '%Y-%m-%d')) as gmtCreate " +
            "from company_base_info c,company_blacklist b,score_type t " +
            "where c.id=b.company_id and b.score_type_id=t.id and b.state=1 " +
            "order by b.gmt_create desc limit #{page},#{limit}")
    List<BlacklistVO> getBlackList(@Param("page")Integer page,
                                   @Param("limit")Integer limit);
    /**
     * 获取黑名单列表 条数
     * @return 结果
     */
    @Select("select count(*) " +
            "from company_base_info c,company_blacklist b,score_type t " +
            "where c.id=b.company_id and b.score_type_id=t.id and b.state=1")
    int getBlackListCount();

    /**
     * 查询企业基本信息
     * @param qyId 企业id
     * @return 结果
     */
    @Select("select c.id as id,c.`name` as `name`,c.business_license_no as businessLicenseNo," +
            "c.register_type AS registerTypeCode,(SELECT c.register_type FROM dic_info WHERE code = c.register_type) AS registerType,c.legal_person as legalPerson," +
            "c.register_address as registerAddress,c.business_date as businessDate," +
            "c.registered_capital as registeredCapital,c.expiration_begin as expirationBegin,c.expiration_end as expirationEnd," +
            "c.main_scope as mainScope " +
            "from company_base_info c where id = #{qyId} ")
    CorpVO getCorpBaseInfo(@Param("qyId")Integer qyId);

    /**
     * 查询企业固定人数
     * @param qyId qyId 企业id
     * @return 结果
     */
    @Select("select count(*) from company_person where company_id=#{qyId} and state=1")
    int getPersonCount(@Param("qyId")Integer qyId);

    /**
     * 查询企业二级职称人数
     * @param qyId 企业id
     * @return 结果集
     */
    @Select("select count(*) from company_person where company_id=#{qyId} and certificate=1 and state=1")
    int getMnanagerCount(@Param("qyId")Integer qyId);

    /**
     * 获取企业信用得分和等级
     * @param qyId 企业id
     * @param year 年度
     * @return 结果
     */
    @Select("select c.`name` as `name`,r.poguid as id,r.all_score as allScore," +
            "r.year as year,r.dj as dj " +
            "from score_record r,company_base_info c " +
            "where c.id=r.poguid and r.year=#{year} and r.poguid=#{qyId}")
    CorpVO getCredit(@Param("qyId")Integer qyId,
                     @Param("year") String year);

    /**
     * 获取项目详细信息
     * @param proId 项目id
     * @return 结果
     */
    @Select("select pro.id as id,pro.project_name as projectName,pro.project_address as " +
            "projectAddress,pro.property_type AS propertyTypeCode,(SELECT name FROM dic_info WHERE code = pro.property_type) AS propertyType,pro.project_area as projectArea," +
            "pro.committee_time as committeeTime,pro.district as district," +
            "(select `name` from district_street where id=pro.district) as districtName," +
            "pro.street as street,(select `name` from district_street where id=pro.street) as streetName," +
            "pro.project_develop as projectDevelop,p.`name` as managerName,c.`name` as qyName," +
            "pro.takeover_time as takeoverTime from company_project pro left join " +
            "(select * from company_person where duties='10806' and state=1) p " +
            "on pro.id=p.project_id left join company_base_info c on c.id=pro.company_id " +
            "where pro.id=#{id}")
    ProjectInfoVO getProjectDetail(@Param("id") Integer proId);

    /**
     * 通过企业名查询企业列表
     * @param page 页码
     * @param dj 等级
     * @param qyName 企业名
     * @return 结果
     */
    @SelectProvider(type = OutsideDaoProvider.class,method = "getCorpList")
    List<CorpVO> getCorpList(@Param("page")Page page,
                             @Param("qyName") String qyName,
                             @Param("dj") String dj);
    /**
     * 通过企业名查询企业列表 条数
     * @param qyName 企业名
     * @param dj 等级
     * @return 结果
     */

    @SelectProvider(type = OutsideDaoProvider.class,method = "getCorpListCount")
    int getCorpListCount(@Param("qyName") String qyName,
                         @Param("dj") String dj);
}
